package com.xbang.attendance.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.xbang.attendance.entity.stock.Inventory;
import com.xbang.attendance.entity.stock.InventoryExchange;
import com.xbang.attendance.service.InventoryExchangeService;
import com.xbang.attendance.utils.DateUtil;
import com.xbang.attendance.utils.FieldUtil;
import com.xbang.core.dao.support.GenericDaoSupport;
import com.xbang.core.service.impl.BaseServiceImpl;
import com.xbang.core.utils.DateUtils;

import enumeration.InventoryOperation;

/**
 * @author huawei
 * @email alonelaval@gmail.com
 */
@Service("invertoryExchangeService")
public class InventoryExchangeServiceImpl extends BaseServiceImpl implements InventoryExchangeService{
	@Autowired
	private GenericDaoSupport genericDaoSupport;
	
	@Override
	public List<InventoryExchange> getInventoryExchangesByInventory(
			Inventory inventory)throws Exception {
		StringBuffer buffer = new StringBuffer("FROM InventoryExchange inventoryExchange WHERE 1 = 1")
					 .append(" and inventoryExchange.sourceWarehouse.id = ?")
					 .append(" and inventoryExchange.product.id =  ?")
					 .append(" and inventoryExchange.operationDate >= ?")
					 .append(" and inventoryExchange.operationDate <= ?");
		List<InventoryExchange> list = genericDaoSupport.getHibernateTemplate().find(buffer.toString(),
				new Object[]{inventory.getWarehouse().getId(),inventory.getProduct().getId(),
				DateUtil.lowerDay(new Date(),DateUtil.getDayOfSum()),new Date()
			});
		return list;
	}
	@Override
	public String genericExcelFile(String webPath,Integer id) throws Exception {
		Inventory inventory = (Inventory) get(Inventory.class, id);
		String genericExcelFile = "warehouse-".concat(DateUtils.format(new Date(), "yyyyMMddhhmmss")).concat(".xls");
		File file = new File(webPath +"//"+ genericExcelFile);
		List orderList = getExchanges(inventory.getWarehouse().getId(),inventory.getProduct().getId(),DateUtil.lowerDay(new Date(),DateUtil.getDayOfSum()));;
		if(!orderList.isEmpty()){
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet sheet = workbook.createSheet("sheet1");
			HSSFRow row = sheet.createRow(0);// 创建第一行
			int i =0;
			for (FieldUtil.Filed field:FieldUtil.getExchangeFileds()) {
				HSSFCell cell = row.createCell(i); 
				cell.setCellValue(field.getName());
				i++;
			}
			
			int rowIndex = 1;
			for (Object object : orderList) {
				row = sheet.createRow(rowIndex++);
				Map m = (Map) object;
				i= 0;
				for(FieldUtil.Filed field:FieldUtil.getExchangeFileds() ){
					HSSFCell cell = row.createCell(i); 
					cell = row.createCell(i);
					if(field.getCode().equals("operation")){
						String  str= m.get(field.getCode()).toString();
						cell.setCellValue(InventoryOperation.valueOf(str).getAliseName());
					}else if(field.getIsDate()){
						cell.setCellValue(m.get(field.getCode()) != null ?DateUtil.formatString(m.get(field.getCode()).toString(),DateUtil.DATE_FORMAT)  : "");
						
					}else{
						cell.setCellValue(m.get(field.getCode()) != null ? m.get(field.getCode()).toString() : "");
						
					}
					i++;
				}
			}
			
			OutputStream os = new FileOutputStream(file);
			workbook.write(os);// 写入
			InputStream in = new FileInputStream(file);
			os.close();
			in.close();
		}
		return genericExcelFile;
	}
	private List getExchanges(Integer warehouseId,Integer productId,Date date) {
		StringBuilder sbBuilder =new StringBuilder("SELECT ex.indent_warehouse_id AS wareCode,")
					 .append(" ware.name AS wareName,")
					 .append(" ex.invertory_operation AS operation,")
					 .append(" pro.product_code AS proCode,")
					 .append(" pro.product_name AS proName,")
					 .append(" ex.amount AS amount,")
					 .append(" ex.operation_date AS opDate")
					 .append(" FROM inventory_exchange ex")
					 .append(" LEFT JOIN product pro ON (ex.product_id = pro.id) ")
					 .append(" LEFT JOIN warehouse ware ON (ex.indent_warehouse_id = ware.id)")
					 .append(" WHERE source_warehouse_id = ? AND ex.product_id = ? ")
					 .append(" AND ex.operation_date >= ? AND ex.operation_date <= NOW()");
		return genericDaoSupport.getJdbcTemplate().queryForList(sbBuilder.toString(),warehouseId,productId,date);
	}
}

